//------------------------------------------------------------------------------
// http://www.codeproject.com/Articles/16934/Using-CLR-integration-to-compress-BLOBs-CLOBs-in-S
//------------------------------------------------------------------------------
using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;

using System.IO;
using System.IO.Compression;

public partial class UserDefinedFunctions
{
    [Microsoft.SqlServer.Server.SqlFunction]
    public static SqlBytes fnIO_DecompressData(SqlBytes compressedBlob)
    {
        if (compressedBlob.IsNull)
            return compressedBlob;

        // Preparing to read data from compressed stream
        DeflateStream decompressor = new DeflateStream(compressedBlob.Stream,
                                           CompressionMode.Decompress, true);

        // Initializing variables
        int bytesRead = 1;
        int chunkSize = 10000;
        byte[] chunk = new byte[chunkSize];

        // Preparing destination stream to hold decompressed data
        MemoryStream decompressedData = new MemoryStream();

        try
        {
            // Reading from compressed stream 
            while ((bytesRead = decompressor.Read(chunk, 0, chunkSize)) > 0)
            {
                // Writting decompressed data
                decompressedData.Write(chunk, 0, bytesRead);
            }
        }
        catch (Exception)
        {
            throw; // Nothing to do...
        }
        finally
        {
            // Cleaning up
            decompressor.Close();
            decompressor = null;
        }

        // Returning a decompressed BLOB
        return new SqlBytes(decompressedData);
    }
}
